﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
using System.IO;
using YAWD.Utils;
using System.Collections;

namespace YAWD.Data
{
    public class Dal : IDisposable
    {
        private static readonly Dal DalInstance = new Dal();
        private static string _connectionString;

        static Dal()
        {
        }
        private Dal()
        {
            _connectionString = ConfigurationManager.ConnectionStrings["YawdContext"].ConnectionString;

            string strPath = Path.GetFullPath(Util.ParseConnectionString(_connectionString));
            if (File.Exists(strPath) == false)
                throw new FileNotFoundException("Can't find Database : " + strPath + ". Please go to options to change it");

        }
        public static Dal GetInstance
        {
            get { return DalInstance; }
        }
        public void CreateConfigurationTable()
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = "CREATE TABLE Configuration (Key VARCHAR(50) NOT NULL, Value VARCHAR(50) NOT NULL)";
            objConnection.Open();
            objCommand.ExecuteNonQuery();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();


        }
        public string GetDbVersion()
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = "Select Value from Configuration where Key='Version'";
            objConnection.Open();

            SQLiteDataReader objResults = objCommand.ExecuteReader(CommandBehavior.SingleRow);
            string strResults=string.Empty;
            if (objResults.HasRows)
            {
                objResults.Read();
                strResults = objResults.GetString(0);
            }

            if (strResults == string.Empty)
                strResults = "0";
            
            objResults.Close();
            objResults.Dispose();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

            return strResults;

        }
        public static string ConnectionString
        {
            get { return _connectionString; }
        }
        public void AddExcludeWallpaper(string filePath)
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = string.Format("INSERT INTO ExcludedFiles (Id,FilePath)VALUES('{0}','{1}')", Guid.NewGuid().ToString(), filePath);
            objConnection.Open();
            objCommand.ExecuteNonQuery();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();


        }
        public void AddIncludeFolder(string folderPath)
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = string.Format("INSERT INTO IncludedFolder (Id,FolderPath)VALUES('{0}','{1}')", Guid.NewGuid().ToString(), folderPath);
            objConnection.Open();
            objCommand.ExecuteNonQuery();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

        }
        public void DeleteIncludeFolder(string folderPath)
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = string.Format("Delete from IncludedFolder where FolderPath='{0}'", folderPath);
            objConnection.Open();
            objCommand.ExecuteNonQuery();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

        }
        public int GetIncludeFolder(string folderPath)
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = string.Format("Select Count(*) from IncludedFolder where FolderPath='{0}'", folderPath);
            objConnection.Open();

            SQLiteDataReader objResults = objCommand.ExecuteReader(CommandBehavior.SingleRow);
            int strResults = 0;

            if (objResults.HasRows)
            {
                objResults.Read();
                strResults = objResults.GetInt32(0);
            }

            objResults.Close();
            objResults.Dispose();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

            return strResults;
        }
        public Boolean GetDonate()
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = "Select Value from Configuration where Key='Donate'";
            objConnection.Open();

            SQLiteDataReader objResults = objCommand.ExecuteReader(CommandBehavior.SingleRow);
            string strResults = string.Empty;
            if (objResults.HasRows)
            {
                objResults.Read();
                strResults = objResults.GetString(0);
            }

            objResults.Close();
            objResults.Dispose();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();


            if (string.IsNullOrWhiteSpace(strResults))
            {
                InsertDonate();
                return false;
            }
            else if (strResults == "False")
                return false;
            else
                return true;
        }
        public IList<string> GetEnabledResolutions()
        {
            List<string> included = null;
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = string.Format("Select Name from Resolution");
            objConnection.Open();

            SQLiteDataReader objResults = objCommand.ExecuteReader();
            if (objResults.HasRows == true)
            {
                included = new List<string>();
                while (objResults.Read())
                    included.Add(objResults.GetString(0));


            }
            objResults.Close();
            objResults.Dispose();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

            return included;
        }
        public int GetExcludeWallPaper(string filepath)
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = string.Format("Select Count(*) from ExcludedFiles where FilePath='{0}'", filepath);
            objConnection.Open();

            SQLiteDataReader objResults = objCommand.ExecuteReader(CommandBehavior.SingleRow);
            int strResults = 0;

            if (objResults.HasRows)
            {
                objResults.Read();
                strResults = objResults.GetInt32(0);
            }

            objResults.Close();
            objResults.Dispose();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

            return strResults;

        }
        public IList<string> GetIncludeFolders()
        {
            List<string> included = null;
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = string.Format("Select FolderPath from IncludedFolder");
            objConnection.Open();

            SQLiteDataReader objResults = objCommand.ExecuteReader();
            if (objResults.HasRows == true)
            {
                included = new List<string>();
                while (objResults.Read())
                    included.Add(objResults.GetString(0));


            }
            objResults.Close();
            objResults.Dispose();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

            return included;
        }
        public string GetIp()
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = "Select Value from Configuration where Key='IP'";
            objConnection.Open();

            SQLiteDataReader objResults = objCommand.ExecuteReader(CommandBehavior.SingleRow);
            string strResults = string.Empty;
            if (objResults.HasRows)
            {
                objResults.Read();
                strResults = objResults.GetString(0);
            }

            objResults.Close();
            objResults.Dispose();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();


            if (string.IsNullOrWhiteSpace(strResults))
            {
                string ip = Util.GetExternalIp();
                if (string.IsNullOrWhiteSpace(ip) == false)
                {
                    InsertIp(ip);
                    return ip;
                }
                else
                    return string.Empty;
            }
            else
                return strResults;
        }
        public int GetLaunch()
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = "Select Value from Configuration where Key='Launch'";
            objConnection.Open();

            SQLiteDataReader objResults = objCommand.ExecuteReader(CommandBehavior.SingleRow);
            string strResults = string.Empty;
            if (objResults.HasRows)
            {
                objResults.Read();
                strResults = objResults.GetString(0);
            }

            objResults.Close();
            objResults.Dispose();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();


            if (string.IsNullOrWhiteSpace(strResults))
            {
                InsertLaunchCount();
                return 1;
            }
            else
                return Convert.ToInt32(strResults);
        }
        public IList GetResolutions()
        {
            List<Resolution> resolutions = null;
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = string.Format("Select Id,Name,Enabled from Resolution");
            objConnection.Open();

            SQLiteDataReader objResults = objCommand.ExecuteReader();
            if (objResults.HasRows == true)
            {
                resolutions = new List<Resolution>();
                while (objResults.Read())
                {
                    Resolution resolution=new Resolution();
                    resolution.Id = objResults.GetString(0);
                    resolution.Name = objResults.GetString(1);
                    resolution.Enabled = objResults.GetBoolean(2);

                    resolutions.Add(resolution);
                }
            }
            objResults.Close();
            objResults.Dispose();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

            return resolutions;
        }

        private void InsertDonate()
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = "INSERT INTO Configuration (Key,Value)VALUES('Donate','False')";
            objConnection.Open();
            objCommand.ExecuteNonQuery();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

        }
        private void InsertIp(string ip)
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = "INSERT INTO Configuration (Key,Value)VALUES('IP','" + ip + "')";
            objConnection.Open();
            objCommand.ExecuteNonQuery();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

        }
        private void InsertLaunchCount()
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = "INSERT INTO Configuration (Key,Value)VALUES('Launch','1')";
            objConnection.Open();
            objCommand.ExecuteNonQuery();
            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

        }

        public void UpdateDonate()
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objConnection.Open();
            objCommand.CommandType = CommandType.Text;

            objCommand.CommandText = "UPDATE Configuration SET Value='True' WHERE Key='Donate';";
            objCommand.ExecuteNonQuery();

            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

        }
        public void UpdateCount(int count)
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objConnection.Open();
            objCommand.CommandType = CommandType.Text;

            objCommand.CommandText = "UPDATE Configuration SET Value='" + count + "' WHERE Key='Launch';";
            objCommand.ExecuteNonQuery();

            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

        }
        public void UpdateResolution(Resolution resolution)
        {
            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = string.Format("UPDATE Resolution Set Enabled=@param1 Where Id='{0}'", resolution.Id);

            SQLiteParameter param1 = new SQLiteParameter("@param1", DbType.Boolean);
            param1.Value = resolution.Enabled;

            objCommand.Parameters.Add(param1);

            objConnection.Open();
            objCommand.ExecuteNonQuery();

            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();
        }
        public void UpgradeDb0To1()
        {

            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objConnection.Open();
            objCommand.CommandType = CommandType.Text;

            objCommand.CommandText = UpgradeScripts.createResolutionsTable;
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
                @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
                Guid.NewGuid().ToString(), "2560x1600");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
                @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
                Guid.NewGuid().ToString(), "2560x1440");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
               @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
               Guid.NewGuid().ToString(), "1920x1200");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
               @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
               Guid.NewGuid().ToString(), "1920x1080");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
               @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
               Guid.NewGuid().ToString(), "1680x1050");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
              @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
              Guid.NewGuid().ToString(), "1600x1200");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
              @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
              Guid.NewGuid().ToString(), "1600x900");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
              @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
              Guid.NewGuid().ToString(), "1440x900");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
              @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
              Guid.NewGuid().ToString(), "1366x768");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
              @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
              Guid.NewGuid().ToString(), "1280x1024");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
              @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
              Guid.NewGuid().ToString(), "1280x960");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
              @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
              Guid.NewGuid().ToString(), "1280x800");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
              @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
              Guid.NewGuid().ToString(), "1280x768");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
              @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
              Guid.NewGuid().ToString(), "1152x864");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
              @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
              Guid.NewGuid().ToString(), "1024x768");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
              @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
              Guid.NewGuid().ToString(), "1024x600");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
              @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
              Guid.NewGuid().ToString(), "800x600");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
              @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
              Guid.NewGuid().ToString(), "640x960");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
             @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
             Guid.NewGuid().ToString(), "320x480");
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = "INSERT INTO Configuration (Key,Value)VALUES('Version','1')";
            objCommand.ExecuteNonQuery();

            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

        }
        public void UpgradeDb0To2()
        {

            SQLiteConnection objConnection = new SQLiteConnection(_connectionString);
            SQLiteCommand objCommand = objConnection.CreateCommand();
            objConnection.Open();
            objCommand.CommandType = CommandType.Text;

            objCommand.CommandText = UpgradeScripts.createResolutionsTable;
            objCommand.ExecuteNonQuery();

            objCommand.CommandText = string.Format(
                @"INSERT INTO Resolution (Id,Name,Enabled)VALUES('{0}','{1}','1')",
                Guid.NewGuid().ToString(), "3402x2268");
            objCommand.ExecuteNonQuery();


            objCommand.CommandText = "INSERT INTO Configuration (Key,Value)VALUES('Version','2')";
            objCommand.ExecuteNonQuery();

            objCommand.Dispose();
            objConnection.Close();
            objConnection.Dispose();

        }

        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }

    }
}
